﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
//using Microsoft.Office.Interop.Excel;

namespace MigrationTool
{
    public class MigrationToolLogic
    {

        //private Workbook _book;

        private SqlConnection _con;

        private List<string> targetTables;

        private System.Data.OleDb.OleDbConnection _excelDBCon;

        private bool _continueOnError;
        private bool _delTableFirst;
        static IDictionary<string, List<FieldModel>> _cachedMetaData = new Dictionary<string, List<FieldModel>>();

        //internal static IDictionary<string, string> _cachedInsertSql = new Dictionary<string, string>();
        //public OLEDBConnection ExcelConnection
        //{
        //    set
        //    {
        //        this._excelDBCon = value;
        //    }
        //}

        public List<string> Targets
        {
            set
            {
                this.targetTables = value;
            }
        }
        public bool DeleteFirst
        {
            set
            {
                _delTableFirst = value;
            }
        }

        public bool ContinueOnError
        {
            set
            {
                _continueOnError = value;
            }
        }

        protected List<FieldModel> GetTableMetaData(string tableName, SqlTransaction transaction)
        {
            List<FieldModel> rtn =null;

            if (_cachedMetaData.TryGetValue(tableName,out rtn))
            {
                return rtn;
            }
            else
            {
                SqlCommand command = _con.CreateCommand();
                command.Transaction = transaction;
                //   transaction = connect.BeginTransaction();
                command.CommandText = String.Format(@"Select so.name tablename,                   --表名字
       sc.name colname,
       columnproperty(sc.id, sc.name, 'IsIdentity') isidentity,
       --CASE WHEN sk.colid=sc.colid THEN 1 ELSE 0 END ispk,
       ISNULL(si.indid,999) keygroup,
       CASE WHEN si.id IS NULL THEN 0 ELSE 1 END isunique,
       --si.id,
              --sk.id keyid,
       --sc.colid sccolid,
      -- sc.type,
      -- sc.xtype,
      --so.id id,
      --,
      
       st.name typename,
       sm.text defaulttext,
       --sm.texttype command,
       sc.prec integerpart,
       sc.scale decimalpart,
       sc.isnullable 
       --sc.colid
       --,sc.id
       --,             --自增字段名字
       --ident_current(so.name) curr_value,    --自增字段当前值
       --ident_incr(so.name) incr_value,       --自增字段增长值
       --ident_seed(so.name) seed_value        --自增字段种子值
  from sysobjects so 
 Inner Join syscolumns sc
    on so.id = sc.id
      --and so.type='U'
       --and columnproperty(sc.id, sc.name, 'IsIdentity') = 1
 INNER JOIN systypes st
 ON sc.xtype=st.xtype AND sc.xusertype = st.xusertype
--LEFT JOIN sysindexkeys sk on sk.id=sc.id AND sk.colid=sc.colid
LEFT JOIN ( SELECT id, indid,colid,is_unique,is_unique_constraint FROM sys.indexes si1
 LEFT JOIN sysindexkeys sk on si1.index_id=sk.indid AND si1.object_id=sk.id
  WHERE (is_unique=1 OR is_unique_constraint=1))   si 
ON  si.id=sc.id AND si.colid=sc.colid
LEFT JOIN syscomments sm ON sm.id=sc.cdefault
--LEFT JOIN sys.extended_properites sg ON sg.
 Where 
 --(si.is_unique=1 OR si.is_unique_constraint=1) AND
 so.type='U' 
 AND so.name in ('{0}')
  ORDER BY  tablename
, isidentity DESC, keygroup asc", tableName);
                //command.Connection = _con;

                using (SqlDataReader reader = command.ExecuteReader())
                {

                    rtn = new List<FieldModel>();
                    FieldModel md;// = new FieldModel();
                    while (reader.Read())
                    {
                        md = new FieldModel();
                        md.Name = reader["colname"] as string;
                        md.IntegerPart = (reader["integerpart"] as short?).Value;
                        md.DefaultSql = reader["defaulttext"] as string;
                        md.FractionalPart = (reader["decimalpart"] as int?);
                        md.IsIdentity = (reader["isidentity"] as int?) ==1;
                        md.IsUnique = (reader["isunique"] as int?) == 1;
                        md.Nullable = (reader["isnullable"] as int?)==1;
                        md.KeyGroup = (reader["keygroup"] as short?).Value;
                        md.Type = reader["typename"] as string;
                        
                        rtn.Add(md);
                    }

                }
                _cachedMetaData[tableName] = rtn;
                return rtn;
            }
        }

        public SqlConnection Connection
        {
            set
            {
                this._con = value;
            }
        }

        public MainForm.Proxy Log
        {
            private get;
            set;
        }

        public MainForm.ProcessInitSet ProcessInitSet
        {
            private get;
            set;
        }
        public MainForm.ProcessSet ProcessSet
        {
            private get;
            set;
        }
        public MainForm.ProcessReSet ProcessReSet
        {
            private get;
            set;
        }

        private void initSet(int max,string txt)
        {
            if (ProcessInitSet != null)
                ProcessInitSet(max, txt);
        }

        private void processSet(int value, string txt=null)
        {
            if (ProcessSet != null)
                ProcessSet(value, txt);
        }
        private void processReSet()
        {
            if (ProcessReSet != null)
                ProcessReSet();
        }
        private void writeLog(string mess,Color? color=null)
        {
            if (Log != null)
                Log(mess, color);
        }

        public MigrationToolLogic(System.Data.OleDb.OleDbConnection book, SqlConnection con)
        {
            this._excelDBCon = book;
            this._con = con;
        }

        public string TemplateFolderPath
        {
            get;
            set;
        }
        public string OutputFolder
        {
            get;
            set;
        }
        public async Task Run(System.Threading.CancellationToken token)
        {
            System.Data.DataTable dtOle =
            _excelDBCon.GetSchema("Tables");
            DataTableReader dtReader = new DataTableReader(dtOle);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            //SqlCommand sqlcommand = new SqlCommand();
            //sqlcommand.Connection = _con;
            

            while (dtReader.Read())
            {
                SqlTransaction transaction = null;
                token.ThrowIfCancellationRequested();
                //if (token.IsCancellationRequested)
                //    throw new OperationCanceledException() ;
                processReSet();
                string tableName = dtReader["Table_Name"] as string;
                if (targetTables.IndexOf(tableName) < 0)
                    continue;
                writeLog("处理工作表" + tableName.Replace("$", "") + "...");
                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + tableName + "]", _excelDBCon);
                tableName = tableName.Replace("$", "");
                objAdapter1.SelectCommand = objCmdSelect;

                System.Data.DataTable dt = new System.Data.DataTable();
                //将Excel中数据填充到数据集
                int i = objAdapter1.Fill(dt);//, tableName.Replace("$", ""));
                try
                {

                    writeLog(string.Format("表{0}中找到{1}行数据", tableName, i), Color.DarkSeaGreen);
                    initSet(i,"开始校验表中数据...");
                    var rm = new RowModel(tableName,_con, GetTableMetaData(tableName, null));
                    //如果与RowModel不一致,则改变dt中列的类型(主要是Identify字段不能和string自动转换)
                    //var nms=from column in dt.Columns  select cls.ColumnName;
                    string idcolName = rm.TryRemoveIdentityColumn(dt);//先移除自增列



                    //string notfountColumn=rm.SetExcelData(dt);
                    //if(!string.IsNullOrEmpty(notfountColumn))
                    //{
                    //    throw new Exception(string.Format("数据库表{0}中未找到列{1}此表操作被取消", tableName, notfountColumn));
                    //}
                    DataColumn[] columns = new DataColumn[dt.Columns.Count];
                    dt.Columns.CopyTo(columns,0);
                    foreach (DataColumn dc in columns)
                    {
                        string colName = dc.ColumnName;
                        if (rm[colName].Type == "uniqueidentifier" && dc.DataType != typeof(Guid))
                        {
                            //dt.Columns.Remove(colName);
                            dc.ColumnName = colName + "_bak001";
                            dt.Columns.Add(new DataColumn() {
                                ColumnName = colName,
                                DataType = typeof(Guid)
                            });
                            for(int m=0;m<dt.Rows.Count;m++)
                            {
                                var drr=dt.Rows[m];
                                if (drr[colName + "_bak001"] != DBNull.Value && !string.IsNullOrEmpty(drr[colName + "_bak001"] as string))
                                {
                                    string s=drr[colName + "_bak001"].ToString();
                                    try
                                    {
                                        drr[colName] = Guid.Parse(s);
                                    }
                                    catch (Exception ex)
                                    {
                                        writeLog(string.Format("表{0}中的第{1}行的列{2}的值不能转化成GUID,已忽略该值:{3}", tableName, m + 2,colName, s), Color.DarkOrange);
                                    
                                    }
                                
                                }
                            }
                            dt.Columns.Remove(colName + "_bak001");
                        }
                            //dc.DataType=typeof(Guid);
                    }
                    //for (int j = 0; j < columns.Length; j++)
                    //{
                    //    for (int n = 0; n < dt.Rows.Count; n++)
                    //    {
                    //        if ("NULL".Equals(dt.Rows[n][j] as string))
                    //            dt.Rows[n][j] = DBNull.Value;
                    //    }
                    //}
                    columns = null;
                    rm.SetExcelData(dt);//匹配excel数据

                    List<string> errorList = null;
                    List<string> warningList = null;

                    if (!string.IsNullOrEmpty(idcolName))
                    {
                        writeLog(string.Format("自增列{0}已排除,如果输入了值,将自动忽略",idcolName),Color.DarkGoldenrod);
                    }

                    DataRow dr;
                    int jj = i;
                    for (i =i - 1; i >= 0; i--)
                    {

                        token.ThrowIfCancellationRequested();
                        dr = dt.Rows[i];
                        if(i%100 == 0)
                            processSet(jj-i);
                        if (!rm.TryValidateRow(dr, _con, out errorList, out warningList))
                        {
                            writeLog(string.Format("第{0}行:", i + 2), Color.DarkRed);
                            foreach (var s in errorList)
                            {
                                writeLog(s, Color.DarkRed);
                            }
                            if (!_continueOnError)
                                throw new Exception("验证错误,请参考输出.");
                            //验证不通过的移除
                            dt.Rows.RemoveAt(i);

                        }
                        //else
                    }

                    transaction = _con.BeginTransaction();
                    if (_delTableFirst)
                    {
                        SqlCommand deleteCom = _con.CreateCommand();
                        deleteCom.Transaction = transaction;
                        deleteCom.CommandText = string.Format("TRUNCATE TABLE {0}", tableName);
                        deleteCom.ExecuteNonQuery();
                        writeLog(string.Format("表{0}中全部数据被删除.", tableName), Color.DarkOrange);
                    }
                    //DataRow[] drs = new DataRow[dt.Rows.Count];
                    i = dt.Rows.Count;
                    if (i > 0)
                    {
                        initSet(10, "往表中插入数据...");

                        using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(_con, SqlBulkCopyOptions.Default, transaction))
                        {
                            sqlBC.BatchSize = 1000;

                            sqlBC.BulkCopyTimeout = 60;
                            sqlBC.DestinationTableName = tableName;
                            //sqlBC.ColumnMappings.Add("valueA", "Value1");
                            //dt.Rows.CopyTo(drs, 0);
                            foreach (DataColumn dc in dt.Columns)
                            {
                                sqlBC.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                            }
                            processSet(2);
                            await sqlBC.WriteToServerAsync(dt, token);
                            dt.Clear();
                        }
                        string tt = string.Format("已向表{0}中插入数据条数{1}", tableName, i);

                        processSet(0, tt);
                        writeLog(tt, Color.DarkSeaGreen);
                    }
                    else
                    {
                        string tt = string.Format("没有向表{0}中插入任何数据", tableName);

                        processSet(0, tt);
                        writeLog(tt, Color.DarkOrange);
                    }

                    
                    transaction.Commit();

                    transaction = null;
                }
                catch (OperationCanceledException)
                {
                    processReSet();
                    if (transaction != null)
                        transaction.Rollback();
                    throw;// new ApplicationException("放弃任务(已插入的数据不受影响)!");
                }
                catch (Exception ex)
                {
                    processReSet();
                    if (transaction != null)
                        transaction.Rollback();
                    writeLog("处理工作表" + tableName + "时出错:\n" + ex.Message + "\n" + (ex.InnerException != null ? ex.InnerException.Message + "\n" : string.Empty) + ex.StackTrace, Color.DarkRed);
                    if (!_continueOnError)
                    {
                        
                        throw new ApplicationException("处理由于错误而终止.");
                    }
                    //transaction.Commit();
                }
                finally
                {
                    dt.Clear();
                }

            }
            
        }
        //public async Task Run1(System.Threading.CancellationToken token)
        //{
        //    System.Data.DataTable dtOle =
        //    _excelDBCon.GetSchema("Tables");
        //    DataTableReader dtReader = new DataTableReader(dtOle);
        //    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
        //    //SqlCommand sqlcommand = new SqlCommand();
        //    //sqlcommand.Connection = _con;
        //    SqlTransaction transaction = null ;
            
        //    while (dtReader.Read()) 
        //    {
        //        if (token.IsCancellationRequested)
        //            break;
        //        string tableName = dtReader["Table_Name"] as string;
        //        writeLog("处理工作表" + tableName.Replace("$", "") + "...");
        //        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + tableName + "]", _excelDBCon);
        //        tableName = tableName.Replace("$", "");
        //        objAdapter1.SelectCommand = objCmdSelect;

        //        System.Data.DataTable dt = new System.Data.DataTable();
        //        //将Excel中数据填充到数据集
        //        objAdapter1.Fill(dt);//, tableName.Replace("$", ""));
        //        try
        //        {
        //            var rm = new RowModel(tableName, GetTableMetaData(tableName, transaction));
        //            List<string> errorList=null;
        //            List<string> warningList = null;
        //            if (_delTableFirst)
        //            {
        //                SqlCommand deleteCom=_con.CreateCommand();
        //                deleteCom.Transaction = transaction;
        //                deleteCom.CommandText = string.Format("TRUNCATE TABLE {0}", tableName);
        //                deleteCom.ExecuteNonQuery();
        //                writeLog(string.Format("表{0}中全部数据被删除.", tableName), Color.DarkOrange);
        //            }
        //            int allcount = 0;
        //            for (int i = 0; i < dt.Rows.Count; i++)
        //            {
        //                if (token.IsCancellationRequested)
        //                    break;
        //                if (rm.TryValidateRow(dt.Rows[i],_con, out errorList,out warningList))
        //                {
        //                    SqlCommand command=rm.CreateInsertSqlCommand(_con);//.SetInsertSqlCommand(ref sqlcommand);
        //                    command.Transaction = transaction;
        //                    allcount +=command.ExecuteNonQuery();
        //                    //sqlcommand.Parameters.Clear();
        //                    //for (int j = 0; j < dt.Columns.Count;j++ )
        //                    //{
        //                    //    sqlcommand.Parameters.Add(new SqlParameter("@" + dt.Columns[j].ColumnName,
        //                    //        rm[dt.Columns[j].ColumnName].Value));
        //                    //    
        //                    //}
        //                }
        //                else
        //                {
        //                    if (!_continueOnError)
        //                    {
        //                        throw new Exception(string.Format("第{0}行:\n{1}", i + 2,string.Join<string>("\n",
        //                            errorList.AsEnumerable<string>())));
        //                    }
        //                    writeLog(string.Format("第{0}行:", i + 2),Color.DarkRed);
        //                    foreach (var s in errorList)
        //                    {
        //                        writeLog(s, Color.DarkRed);
        //                    }

        //                }
        //                foreach (var w in warningList)
        //                {
        //                    writeLog(w, Color.DarkOrange);
        //                }

        //            }

        //            writeLog(string.Format("表{0}中插入数据{1}条.", tableName,allcount),Color.DarkSeaGreen);
        //        }
        //        catch (Exception ex)
        //        {
        //            writeLog("处理工作表" + tableName + "时出错:\n" + ex.Message + "\n" + ex.StackTrace);
        //            if (!_continueOnError)
        //            {
        //                transaction.Rollback();
        //                throw new ApplicationException("处理由于错误而终止.");

        //            }
                        
        //        }
        //        finally
        //        {
        //            dt.Clear();
        //            //objDataset1.Clear();
        //        }
        //    }
        //    transaction.Commit();
        //    //transaction.Commit();

        //    //foreach(Worksheet sh in _book.Worksheets)
        //    //{
        //    //    writeLog("正在处理工作表:" + sh.Name);
        //    //    try
        //    //    {
        //    //        bool? _istar = sh.Cells[3, "G"].Value;
        //    //        if (_istar.HasValue && _istar.Value == false)
        //    //        {
        //    //            writeLog("工作表:" + sh.Name + " 非生成目标,因[Is Target]设为FALSE",Color.DarkOrange);
        //    //        }
        //    //        else
        //    //        {

        //    //            string _tns = sh.Cells[4, "G"].Value;
        //    //            if (string.IsNullOrWhiteSpace(_tns))
        //    //            {
        //    //                writeLog("工作表:" + sh.Name + " 没有设定[Template Name],对本工作表的处理终止.", Color.DarkOrange);
        //    //            }
        //    //            else
        //    //            {
        //    //                ClassModel cmodel = new ClassModel();
        //    //                cmodel.Description = sh.Cells[2, "G"].Value;

        //    //                cmodel.Creator = sh.Cells[4, "AN"].Value;
        //    //                DateTime? _d = sh.Cells[4, "AR"].Value;
        //    //                cmodel.CreateDate = _d.HasValue ? _d.Value : DateTime.Today;
        //    //                //cmodel.IsTarget = _istar.HasValue ? _istar.Value : true;//sh.Cells[3, "G"].Value;
        //    //                cmodel.Version = sh.Cells[4, "AV"].Value;
        //    //                cmodel.Name = sh.Name;
        //    //                IList<FeildModel> _templates = new List<FeildModel>();
        //    //                cmodel.Fields = _templates;
        //    //                int row = 8;
        //    //                FeildModel fm = null;
        //    //                while (sh.Cells[row, "K"].Value != null)
        //    //                {
        //    //                    fm = new FeildModel();
        //    //                    fm.Label = sh.Cells[row, "C"].Value;
        //    //                    fm.Name = sh.Cells[row, "K"].Value;

        //    //                    fm.Type = sh.Cells[row, "V"].Value ;
        //    //                    bool? _nab = sh.Cells[row, "AB"].Value as bool?;
        //    //                    fm.Nullable = _nab.HasValue ? _nab.Value : true;// sh.Cells[row, "K"].Value;
        //    //                    _nab = sh.Cells[row, "AE"].Value  as bool?;
        //    //                    fm.DataMember = _nab.HasValue ? _nab.Value : true;// sh.Cells[row, "K"].Value;
        //    //                    fm.Memo = sh.Cells[row, "AQ"].Value;
        //    //                    double? _int = sh.Cells[row, "AH"].Value as double?;
        //    //                    fm.IntegerPart = _int.HasValue ? (int)_int.Value : 10;
        //    //                    _int = sh.Cells[row, "AK"].Value as double?;
        //    //                    fm.FractionalPart = _int.HasValue ? (int)_int.Value : 2;
        //    //                    fm.DecimalValidator = sh.Cells[row, "AN"].Value as string;
        //    //                    if (fm.DecimalValidator == null && fm.Type == "decimal?")
        //    //                        fm.DecimalValidator = "GreateEqualsZero";
        //    //                    _templates.Add(fm);
        //    //                    row++;
        //    //                }
        //    //                foreach(var ss in _tns.Split(','))
        //    //                {
        //    //                    //TemplateDocument document= TemplateDocument.FromFileCache(TemplateFolderPath+"\\" + ss, Encoding.UTF8);
        //    //                    TemplateDocument document = new TemplateDocument(TemplateFolderPath + "\\" + ss, Encoding.UTF8);
        //    //                    document.RegisterGlobalFunction(this.GetLowerCapfirst);
        //    //                    document.RegisterGlobalFunction(this.ToYMD);
        //    //                    document.Variables.SetValue("model", cmodel);
        //    //                    //_templates.Add(ss);
        //    //                    string fileName = OutputFolder + "\\" + ss.Split('.')[0];
        //    //                    if (!System.IO.Directory.Exists(fileName))
        //    //                        System.IO.Directory.CreateDirectory(fileName);
        //    //                    fileName +="\\" + cmodel.Name + ss;
        //    //                    using (TextWriter tw = File.CreateText(fileName))
        //    //                    {
        //    //                        document.Render(tw);
        //    //                    }
        //    //                    writeLog("对工作表:" + sh.Name + "的模板{" + ss + "}处理成功,生成文件:\n" + fileName);
        //    //                }
        //    //            }


        //    //            writeLog("完成处理工作表:" + sh.Name);
        //    //        }

        //    //    }
        //    //    finally
        //    //    {
        //    //        System.Runtime.InteropServices.Marshal.ReleaseComObject(sh);
        //    //    }

                
        //    //}
        //}


        //private object GetLowerCapfirst(object[] args)
        //{
        //    if (args.Length == 1)
        //    {
        //        string arg = args[0] as string;
        //        if (!string.IsNullOrWhiteSpace(arg))
        //        {
        //            return arg.Substring(0, 1).ToLower()+arg.Substring(1);
        //        }
        //    }
        //    throw new ArgumentException("GetFormattedField函数只接收1个非空的string类型参数!");
        //}
        //private object ToYMD(object[] args)
        //{
        //    if (args.Length == 1)
        //    {
        //        DateTime? arg = args[0] as DateTime?;
        //        return arg.Value.ToString("yyyy-MM-dd");
        //    }
        //    throw new ArgumentException("GetFormattedField函数只接收1个非空的string类型参数!");
        //}


        //internal Task Run(System.Threading.CancellationToken cancellationToken)
        //{
        //    throw new NotImplementedException();
        //}
    }
}
